/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package co.springtree.spectrum.visa.persistence.dao;

import co.springtree.spectrum.visa.persistence.entities.CommandDTO;
import co.springtree.spectrum.visa.persistence.entities.CommandRuleDTO;
import co.springtree.spectrum.visa.persistence.entities.RuleDTO;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import org.apache.log4j.Logger;

/**
 *
 * @author Ricardo
 */
public class CommandRuleDAO extends AbstractDAO<CommandRuleDTO>{
    private static Logger logger = Logger.getLogger("CommandRuleDAO");
    
    @Override
    public void insert(CommandRuleDTO object) throws SQLException {
        try {
            connection = getConnectionPool().getConnection();
            sQLQuery = "INSERT INTO commandrule (ruleName,commandName) VALUES (?,?)";
            statement = connection.prepareStatement(sQLQuery);
            statement.setString(1, object.getRule().getRuleName());
            statement.setString(2, object.getCommand().getCommandName());
            statement.executeUpdate();
        } catch (SQLException e) {
            logger.error(e);
        } finally {
            getConnectionPool().freeConnection(connection);
        }
    }

    @Override
    public void update(CommandRuleDTO object) throws SQLException {
        try {
            connection = getConnectionPool().getConnection();
            sQLQuery = "UPDATE commandrule ruleName = ? ,commandName =? WHERE ruleName = ? AND commandName =? ";
            statement = connection.prepareStatement(sQLQuery);
            statement.setString(1, object.getRule().getRuleName());
            statement.setString(2, object.getCommand().getCommandName());
            statement.setString(3, object.getRule().getRuleName());
            statement.setString(4, object.getCommand().getCommandName());
            statement.executeUpdate();
        } catch (SQLException e) {
            logger.error(e);
        } finally {
            getConnectionPool().freeConnection(connection);
        }
    }

    @Override
    public void delete(CommandRuleDTO object) throws SQLException {
        try {
            connection = getConnectionPool().getConnection();
            sQLQuery = "DELETE FROM commandrule WHERE id = ?";
            statement = connection.prepareStatement(sQLQuery);
            statement.setInt(1, object.getId());
            statement.executeUpdate();
        } catch (SQLException e) {
            logger.error(e);
        } finally {
            getConnectionPool().freeConnection(connection);
        }
    }

    @Override
    public CommandRuleDTO select(CommandRuleDTO object) throws SQLException {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public Collection<CommandRuleDTO> selectAll() throws SQLException {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }
   
    public Collection<CommandRuleDTO> selectAll(RuleDTO rule) throws SQLException {
        Collection<CommandRuleDTO> commandRuleColl = new ArrayList<CommandRuleDTO>();
        CommandDAO commandDAO = new CommandDAO();
        try {
            connection = getConnectionPool().getConnection();
            sQLQuery = "SELECT * FROM commandrule WHERE idrule=?";
            statement = connection.prepareStatement(sQLQuery);
            rs = statement.executeQuery();
            while (rs.next()) {
                CommandRuleDTO commandRule = new CommandRuleDTO();
                commandRule.setRule(rule);
                CommandDTO command = new CommandDTO();
                command.setCommandName(rs.getString("commandName"));
                command = commandDAO.select(command);
                commandRule.setCommand(command);
                commandRuleColl.add(commandRule);
            }
        } catch (SQLException e) {
            logger.error(e);
        } finally {
            getConnectionPool().freeConnection(connection);
        }

        return commandRuleColl;
    }

    @Override
    public void createTable(boolean dropTable) throws Exception {
    try {
            connection = getConnectionPool().getConnection();
            sQLQuery = null;
            if (dropTable == true) {
                sQLQuery = "DROP TABLE IF EXISTS commandrule";
                statement = connection.prepareStatement(sQLQuery);
                statement.executeUpdate();
            }
            sQLQuery = "CREATE TABLE commandrule (" +
            "  id int(11) NOT NULL," +
            "  rulename varchar(255) NOT NULL," +
            "  commandname varchar(255) NOT NULL," +
            "  PRIMARY KEY (id)," +
            "  KEY fkcommand_idx (commandname)," +
            "  KEY fkrule_idx (rulename)," +
            "  CONSTRAINT fkcommand FOREIGN KEY (commandname) REFERENCES command (commandname) ON UPDATE CASCADE," +
            "  CONSTRAINT fkrule FOREIGN KEY (rulename) REFERENCES rule (rulename) ON UPDATE CASCADE" +
            ") ENGINE=InnoDB";

            statement = connection.prepareStatement(sQLQuery);
            statement.executeUpdate();
        } catch (SQLException e) {
            logger.error(e);
        } finally {
            getConnectionPool().freeConnection(connection);
        }
    }
    
}
